﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using BioModel;

namespace BioDAL
{
    [DataContract]
    public class SalgDAL : ISalgDAL
    {
        private SalgSaedeDAL dbSalgSaede;
        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region Salg
        public void InsertSalg(DateTime datoOprettet, int antalBilletter, string salgsType, double totalPris, int forestilling, int kundeTlf, List<Saede> s)
        {
            Salg salg = new Salg(datoOprettet, antalBilletter, salgsType, totalPris, forestilling, kundeTlf, s);
            try
            {
                string sql = string.Format("set dateformat dmy Insert into bioSalg" +
                     "(datoOprettet, antalBilletter, salgsType, totalPris, forestilling, kundeTlf) Values" +
                     "('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", datoOprettet, antalBilletter, salgsType, totalPris, forestilling, kundeTlf);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                    foreach (Saede saede in s)
                    {
                        dbSalgSaede.InsertSalgSaede(saede.SaedeId, salg.SalgId);
                    }
                }
            }
            catch (Exception se)
            {
                Console.WriteLine("!SQL Insert Salg fejl: " + se);
            }
        }
        #endregion


        public Salg GetEnSalg(int salgId)
        {
            Salg salg = new Salg();
            string sql = string.Format("Select * from bioSalg where salgId = {0}", salgId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    salg = (BuildSalg(dr));

                }
                dr.Close();
            }
            return salg;
        }

        public List<Salg> GetAlleSalg(int telefonNr)
        {
            List<Salg> salgListe = new List<Salg>();
            string sql = string.Format("Select * From bioSalg where kundeTlf = '{0}'", telefonNr);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    salgListe.Add(BuildSalg(dr));
                }
                dr.Close();
            }
            return salgListe;
        }

        private static Salg BuildSalg(SqlDataReader dr)
        {
            Salg salg = new Salg(
                (int)(dr["salgId"]),
                (DateTime)(dr["datoOprettet"]),
                (int)(dr["antalBilletter"]),
                (string)(dr["salgsType"]),
                (double)(dr["totalPris"]),
                (int)(dr["forestilling"]),
                (int)(dr["kundeTlf"])
                );
             return salg;
        }

        public void EditSalg(int telefonNr, string salgsType)
        {
            string sql = string.Format("Update bioSalg Set salgsType = '{0}' Where kundeTlf = {1}"
                , salgsType, telefonNr);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }
}
